	//116 tutorial.
	//http://www.youtube.com/watch?NR=1&v=WKf5CvuQwAQ
	//

	package databases;

	import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

	public class UserBookDB {

		public static final String KEY_ROWID = "_id";
		public static final String KEY_USERID = "user_id";
		public static final String KEY_BOOKID = "book_id";

		private static final String DATABASE_NAME = "userBookDB";
		private static final String DATABASE_TABLE = "userBookTable";
		private static final int DATABASE_VERSION = 3;

		private DbHelper ourHelper;
		private final Context ourContext;
		private SQLiteDatabase ourDatabase;

		private static class DbHelper extends SQLiteOpenHelper {

			public DbHelper(Context context) {
				super(context, DATABASE_NAME, null, DATABASE_VERSION);
			}

			// this method creates our database! this is only called once.
			@Override
			public void onCreate(SQLiteDatabase db) {

				// TODO Auto-generated method stub
				// creating the database table. specifying the table name and
				// different variables in the table.
				db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
						+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_USERID
						+ " TEXT NOT NULL, " + KEY_BOOKID + " TEXT NOT NULL);");
			}

			// if oncreate() is called, onupgrade() is called instead.
			@Override
			public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
				// TODO Auto-generated method stub
				db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
				onCreate(db);

			}
		}

		public UserBookDB(Context c) {
			ourContext = c;
		}

		public UserBookDB open() throws SQLException {
			ourHelper = new DbHelper(ourContext);
			ourDatabase = ourHelper.getWritableDatabase();
			return this;
		}

		// closes SQLiteOpenHelper
		public void close() {
			ourHelper.close();
		}

		public long createEntry(long bookId, long userId ) {
			// TODO Auto-generated method stub
			ContentValues cv = new ContentValues();
			cv.put(KEY_USERID, userId);
			cv.put(KEY_BOOKID, bookId);
			// insert data into database
			return ourDatabase.insert(DATABASE_TABLE, null, cv);
		}

		public String getData() {
			// TODO Auto-generated method stub
			String[] columns = new String[] { KEY_ROWID, KEY_USERID, KEY_BOOKID };

			Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
					null, null); // possible to enter other info in null

			String result = "";

			int iRow = c.getColumnIndex(KEY_ROWID);
			int iUserId = c.getColumnIndex(KEY_USERID);
			int iBookId = c.getColumnIndex(KEY_BOOKID);

			// loop that iterates through the db list.
			for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
				result = result + c.getString(iRow) + " User Id: " + c.getString(iUserId)
						+ " Book Id: " + c.getString(iBookId) + "\n";

			}
			return result;
		}

		public ArrayList<Long> getBookIds(long userId){
			
			ArrayList<Long>  bookIdList= new ArrayList<Long>();
			
			String[] columns = new String[] { KEY_ROWID, KEY_USERID, KEY_BOOKID };       
	        Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
			
	        int bookId = c.getColumnIndex(KEY_BOOKID);
			int thisUserId = c.getColumnIndex(KEY_USERID);
			c.moveToPosition(1);
			
			for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
				
				if( c.getLong(thisUserId) == (userId)){
					
					long id = c.getLong(bookId);
					bookIdList.add(id);
					
					}
			}
			
			return bookIdList;
			
		}

		public void deleteUserBook(Long i) {
			
			ourDatabase.delete(DATABASE_TABLE, KEY_BOOKID + "=" + i, null);
			
		}

		public void removeAllConnections() {
			ourDatabase.delete(DATABASE_TABLE, null, null);
			
		}
		
	}
